﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DTO;
namespace DataAccess_Layer
{
    public class Customer_DAC
    {
        DataClasses_RetailerDataContext context = DataAccessHelper.DB;
        public int CountList()
        {
            var query = (from cus in context.Customers select cus).Distinct().Count();
            return query;
        }
        public void InsertCustomer(CustomerDTO cu)
        {
            Customer cus = new Customer();
            cus.FullName = cu.Name;
            cus.Address = cu.Address;
            cus.PersonalID = cu.PersonalID;
            cus.SS = cu.SS;
            cus.UserName = cu.UserName;
            context.Customers.InsertOnSubmit(cus);
            DataAccessHelper.Save();
        }
        public IList<CustomerDTO> getListAll()
        {
            var query = (from cus in context.Customers
                         select new CustomerDTO
                         {
                             Name = cus.FullName,
                             UserName = cus.UserName,
                             ID = cus.ID,
                             Password = cus.Password,
                             PersonalID = cus.PersonalID,
                             Address = cus.Address,
                             SS = (long)cus.SS,
                         }
                ).Distinct<CustomerDTO>();
            return query.ToList<CustomerDTO>();
        }
        public static List<CustomerDTO> getAll()
        {
            List<CustomerDTO> customers = null;
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Select_All_Customer");

                if (rdr.HasRows)
                {
                    customers = new List<CustomerDTO>();
                    while (rdr.Read())
                    {
                        CustomerDTO cus = createCustomer(rdr);
                        customers.Add(cus);
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return customers;
        }
        private static CustomerDTO createCustomer(SqlDataReader rdr)
        {
            CustomerDTO cus = new CustomerDTO();
            cus.UserName = (string)rdr["UserName"];
            cus.ID = (long)rdr["ID"];
            cus.Name = rdr["FullName"] == null ? "" : rdr["FullName"].ToString();
            cus.Password = rdr["Password"] == null ? "" : rdr["Password"].ToString();
            cus.PersonalID = rdr["PersonalID"] == null ? "" : rdr["PersonalID"].ToString();
            cus.Address = rdr["Address"] == null ? "" : rdr["Address"].ToString();
            cus.SS = 9999999;
            cus.Deleted = rdr["IsDeleted"] == null ? 0 : (int)rdr["IsDeleted"];
            cus.IsBanned = rdr["IsBanned"] == null ? false : (bool)rdr["IsBanned"];
            return cus;
        }

        /// <summary>
        /// Get Customer by Username
        /// </summary>
        /// <param name="userName"></param>
        /// <returns>Nếu không tìm thấy username thì trả về null ngược lại trả về customer</returns>
        public static CustomerDTO getByUserName(string userName)
        {
            List<SqlParameter> sqlParams = makeGetByUserNameParams(ref userName);
            CustomerDTO customer = null;
            DataAccessHelper helper = null;
            SqlDataReader rdr = null;

            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Get_Customer_By_Username", sqlParams);

                if (rdr.HasRows)
                {
                    if (rdr.Read())
                    {
                        customer = createCustomer(rdr);
                    }
                }
            }
            finally
            {
                helper.close();
                rdr.Close();
            }

            return customer;
        }
        private static List<SqlParameter> makeGetByUserNameParams(ref string userName)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            SqlParameter inParam = new SqlParameter("@UserName", SqlDbType.NVarChar, 20);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = userName;
            sqlParams.Add(inParam);
            return sqlParams;
        }

        public static bool insert(CustomerDTO newCustomer)
        {
            List<SqlParameter> sqlParams = makeInsertParams(newCustomer);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int count;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Insert_Customer", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                count = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (count == 1);
        }
        private static List<SqlParameter> makeInsertParams(CustomerDTO newCustomer)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@UserName", SqlDbType.NVarChar);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.UserName;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@FullName", SqlDbType.NVarChar);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.Name;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@Pass", SqlDbType.NVarChar);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.Password;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@PersonalID", SqlDbType.NVarChar);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.PersonalID;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@Address", SqlDbType.NVarChar);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.Address;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@SS", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = newCustomer.SS;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static bool customerIsExisted(string userName)
        {
            List<SqlParameter> sqlParams = makeCheckExistedParams(ref userName);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int existed;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Check_Existed_Customer", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                existed = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (existed == 1);
        }
        private static List<SqlParameter> makeCheckExistedParams(ref string userName)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();
            SqlParameter inParam = new SqlParameter("@UserName", SqlDbType.NVarChar, 20);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = userName;
            sqlParams.Add(inParam);
            return sqlParams;
        }

        public static bool chargeAccount(long customerID, long cardID, string cardCode)
        {
            List<SqlParameter> sqlParams = makeChargeAccountParams(ref customerID, ref cardID, ref cardCode);

            DataAccessHelper helper = null;
            SqlDataReader rdr = null;
            int result;
            try
            {
                helper = new DataAccessHelper();
                rdr = helper.excuteSP("SP_Charge_Customer_Account", sqlParams);
            }
            finally
            {
                helper.close();
                rdr.Close();

                // If your Command contains output parameters or return values, they will not be available until the DataReader is closed.            
                result = (int)sqlParams[sqlParams.Count - 1].Value;
            }

            return (result == 1);
        }
        private static List<SqlParameter> makeChargeAccountParams(ref long customerID, ref long cardID, ref string cardCode)
        {
            List<SqlParameter> sqlParams = new List<SqlParameter>();

            SqlParameter inParam = new SqlParameter("@cardCode", SqlDbType.VarChar, 20);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = cardCode;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@customerID", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = customerID;
            sqlParams.Add(inParam);

            inParam = new SqlParameter("@cardID", SqlDbType.BigInt);
            inParam.Direction = ParameterDirection.Input;
            inParam.Value = cardID;
            sqlParams.Add(inParam);

            return sqlParams;
        }

        public static object tim(string tkTenDN, string tkTen, string tkEmail, string tkDiaChi)
        {
            //4 cai null
            if (tkTenDN != null && tkTen != null && tkEmail != null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.FullName.Contains(tkTen) && pp.Email.Contains(tkEmail) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }

            //1 cai null
            if (tkTenDN == null && tkTen != null && tkEmail != null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.FullName.Contains(tkTen) && pp.Email.Contains(tkEmail) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen == null && tkEmail != null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.Email.Contains(tkEmail) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen != null && tkEmail == null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.FullName.Contains(tkTen) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen != null && tkEmail != null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.FullName.Contains(tkTen) && pp.Email.Contains(tkEmail)).Select(pp => pp);
            }

            //2 cai null
            if (tkTenDN == null && tkTen == null && tkEmail != null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.Email.Contains(tkEmail) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN == null && tkTen != null && tkEmail == null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.FullName.Contains(tkTen) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN == null && tkTen != null && tkEmail != null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.FullName.Contains(tkTen) && pp.Email.Contains(tkEmail)).Select(pp => pp);
            }

            if (tkTenDN != null && tkTen == null && tkEmail == null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen == null && tkEmail != null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.Email.Contains(tkEmail)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen != null && tkEmail == null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN) && pp.FullName.Contains(tkTen)).Select(pp => pp);
            }

            //3 cai null
            if (tkTenDN == null && tkTen == null && tkEmail == null && tkDiaChi != null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.Address.Contains(tkDiaChi)).Select(pp => pp);
            }
            if (tkTenDN != null && tkTen == null && tkEmail == null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.UserName.Contains(tkTenDN)).Select(pp => pp);
            }
            if (tkTenDN == null && tkTen != null && tkEmail == null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.FullName.Contains(tkTen)).Select(pp => pp);
            }
            if (tkTenDN == null && tkTen == null && tkEmail != null && tkDiaChi == null)
            {
                return DataAccessHelper.DB.Customers.Where(pp => pp.Email.Contains(tkEmail)).Select(pp => pp);
            }

            return layDanhSach();
        }

        private static object layDanhSach()
        {
            return DataAccessHelper.DB.Customers.Select(pp => pp);
        }

        public static void delete(List<long> deletedIDs)
        {
            foreach (long deletedID in deletedIDs)
            {
                Customer cus = DataAccessHelper.DB.Customers.Where(c => c.ID == deletedID).Select(c => c).First();
                DataAccessHelper.DB.Customers.DeleteOnSubmit(cus);
            }

            DataAccessHelper.Save();
        }

        public static void banNick(List<long> bannedIds)
        {
            foreach (long bannedId in bannedIds)
            {
                Customer cus = DataAccessHelper.DB.Customers.Where(c => c.ID == bannedId).Select(c => c).First();
                cus.IsBanned = true;                
            }

            DataAccessHelper.Save();
        }

        public static void boBanNick(List<long> unbannedIds)
        {
            foreach (long bannedId in unbannedIds)
            {
                Customer cus = DataAccessHelper.DB.Customers.Where(c => c.ID == bannedId).Select(c => c).First();
                cus.IsBanned = false;
            }

            DataAccessHelper.Save();
        }

        public static void update(Customer p)
        {
            Customer cus = DataAccessHelper.DB.Customers.Where(c => c.ID == p.ID).Select(c => c).First();
            cus.UserName = p.UserName;
            cus.Address = p.Address;
            cus.Email = p.Email;
            cus.FullName = p.FullName;
            cus.IsBanned = p.IsBanned;
            cus.DaXoa = p.DaXoa;
            cus.PersonalID = p.PersonalID;
            cus.NgayGiaNhap = p.NgayGiaNhap;

            DataAccessHelper.Save();
        }

        public static Customer lay(long id)
        {
            return DataAccessHelper.DB.Customers.Where(c => c.ID == id).Select(c => c).First();
        }
    }
}
